package com.example.secondhandsysapi.Mapper;

import com.example.secondhandsysapi.Entity.CommentInfo;
import com.example.secondhandsysapi.Vo.Comment;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import com.example.secondhandsysapi.Vo.UserInfoCommentInfo;


import java.util.Date;
import java.util.List;

@Mapper
public interface CommentInfoMapper {

    //获取评论信息修改后进行审核的内容
    @Select("select comment_id,comment_description,comment_time,uppertest_status,check_info from comment_info where uppertest_status = 0 and comment_description like '%${comment_description}%'")

    List<CommentInfo> getAllUpdateCommentInfo(@Param("comment_description") String comment_description);

    //对status进行修改
    @Select("UPDATE comment_info SET uppertest_status = 1 WHERE comment_id = #{comment_id}")
    Integer updateStatus(@Param("comment_id") Integer comment_id);

    //对拒绝填写的备注进行保存
    @Select("UPDATE comment_info SET check_info = #{check_info},uppertest_status = 2 WHERE comment_id=#{comment_id}")
    Integer updateCheck_Info(@Param("check_info") String check_info,@Param("comment_id") Integer comment_id );

    @Select("select  user_info.user_name, comment_info.* from user_info join comment_info on user_info.user_id = comment_info.user_id where user_info.user_id =#{id}   ")
    List<UserInfoCommentInfo>  getAllCommentInfoAndUserInfo(Integer id);
        //计算打分平均值得到所有的user_id的信誉等级信息
    @Select("SELECT comment_info.user_id,user_name,\n" +
                "       AVG(goods_mark )  as goods_mark ,\n" +
                "       AVG( logistics_mark )  as logistics_mark ,\n" +
                "       AVG( service_mark) as service_mark ,\n" +
                "       CASE\n" +
                "           WHEN (AVG(goods_mark  ) + AVG(logistics_mark) + AVG( service_mark)) / 3 BETWEEN 0 AND 1 THEN '一星级'\n" +
                "           WHEN (AVG(goods_mark  ) + AVG(logistics_mark) + AVG( service_mark)) / 3 BETWEEN 1 AND 2 THEN '二星级'\n" +
                "           WHEN (AVG(goods_mark  ) + AVG(logistics_mark) + AVG( service_mark)) / 3 BETWEEN 2 AND 3 THEN '三星级'\n" +
                "\t\t   WHEN (AVG(goods_mark  ) + AVG(logistics_mark) + AVG( service_mark)) / 3 BETWEEN 3 AND 4 THEN '四星级'\n" +
                "\t\t   WHEN (AVG(goods_mark  ) + AVG(logistics_mark) + AVG( service_mark)) / 3 BETWEEN 4 AND 5 THEN '五星级'\n" +
                "           ELSE '未定义星级'\n" +
                "       END AS status\n" +
                "FROM comment_info    join user_info on comment_info.user_id = user_info.user_id\n" +
                "GROUP BY user_id")
        List<UserInfoCommentInfo> getAll();


        //获取商品详情页的评论信息
    @Select("INSERT INTO comment_info (user_id, goods_id, comment_description, goods_mark, logistics_mark, service_mark, comment_time, uppertest_status)  \n" +
            "VALUES (#{user_id}, #{goods_id}, #{comment_description}, #{goods_mark}, #{logistics_mark}, #{service_mark}, NOW(), 0)")
    Integer InsertComment(@Param("user_id") Integer user_id,@Param("goods_id") Integer goods_id,@Param("comment_description") String comment_description,@Param("goods_mark") Integer goods_mark,@Param("logistics_mark") Integer logistics_mark,@Param("service_mark") Integer service_mark);

    //获取显示的商品评论
    @Select("select comment_id,goods_id,comment_description,status,uppertest_status,comment_info.user_id,user_name,user_image from comment_info join user_info  where  comment_info.user_id = user_info.user_id and goods_id=#{goods_id} and comment_info.uppertest_status=1")
    List<Comment> showCommentInfo(@Param("goods_id") Integer goods_id);



    //个人中心信誉显示
    @Select("SELECT comment_info.user_id,\n" +
                "AVG(goods_mark ) as goods_mark,\n" +
                "AVG( logistics_mark )  as logistics_mark,\n" +
                "AVG( service_mark) as service_mark,\n" +
                "CASE\n" +
                "WHEN (AVG(goods_mark  ) + AVG(logistics_mark) + AVG( service_mark)) / 3 BETWEEN 0 AND 1 THEN '一星级'\n" +
                "WHEN (AVG(goods_mark  ) + AVG(logistics_mark) + AVG( service_mark)) / 3 BETWEEN 1 AND 2 THEN '二星级'\n" +
                "WHEN (AVG(goods_mark  ) + AVG(logistics_mark) + AVG( service_mark)) / 3 BETWEEN 2 AND 3 THEN '三星级'\n" +
                "WHEN (AVG(goods_mark  ) + AVG(logistics_mark) + AVG( service_mark)) / 3 BETWEEN 3 AND 4 THEN '四星级'\n" +
                "WHEN (AVG(goods_mark  ) + AVG(logistics_mark) + AVG( service_mark)) / 3 BETWEEN 4 AND 5 THEN '五星级'\n" +
                "ELSE '未定义星级'\n" +
                "END AS status\n" +
                "FROM comment_info \n" +
                "where user_id=#{user_id}")
    List<CommentInfo> getUserCommentInfo(@Param("user_id") Integer user_id);

    }









